from math import asin
from numpy.core.umath import radians
from numpy.ma import sin, cos, sqrt
from sqlalchemy import create_engine
import pandas as pd

class GPS(object):

    def __init__(self):

        self.length = 1000
        self._conn = self.get_oracle_engine()
        self.earth_R = 6371.393

    def get_oracle_data(self, start, end):
        df = pd.read_sql_query(
            """ select *
                  from index_transport_gps t
                 where t.up_time >= to_date('2018-06-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
                   and t.up_time <= to_date('2018-06-30 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
                   and to_char(t.up_time, 'hh24:mi:ss') >=
                       to_char(to_date('2018-06-01 02:15:00', 'yyyy-MM-dd hh24:mi:ss'),
                               'hh24:mi:ss')
                   and to_char(t.up_time, 'hh24:mi:ss') <=
                       to_char(to_date('2018-06-30 04:45:00', 'yyyy-MM-dd hh24:mi:ss'),
                               'hh24:mi:ss')                             
                               and t.speed>10 and t.speed<150
                               order by t.plate_num,t.up_time """,
             # and t.plate_num = '冀T55889'
             #  and t.speed>10 and t.speed<150
            self._conn )
        return df

    def get_oracle_engine(self):
        user = 'tocc_shijiazhuang'
        password = 'sim2013'
        ip = '10.24.89.221'
        port = '1521'
        instance = 'orcl'
        engine_str = 'oracle://%s:%s@%s:%s/%s' % (user, password, ip, port, instance)
        engine = create_engine(engine_str, encoding='utf-8')
        return engine

    def add_rows(self,x):
        x['distance'] = self._cal_distance(x.longitude, x.latitude, x.longitude.shift(1), x.latitude.shift(1))
        return x['distance'].sum()


    def get_gps_car(self):

        gps = self.get_oracle_data(None, None)
        gps['date'] = gps['up_time'].dt.date
        gps = gps.groupby(['plate_num', 'date']).apply(self.add_rows).reset_index()
        gps.columns = ['plate_num', "length"]
        gps = gps[gps['length'] > self.length]
        gps['result'] =  gps['plate_num'] +'|'+gps['']
        print(gps['plate_num'].tolist())

    def _cal_distance(self, lon1, lat1, lon2, lat2): # 经度1，纬度1，经度2，纬度2 （十进制度数）
        """
        This method is for Series;
        Calculate the great circle distance between two points
        on the earth (specified in decimal degrees)
        """
        # 将十进制度数转化为弧度
        lon1, lat1, lon2, lat2 = lon1.apply(radians), lat1.apply(radians), lon2.apply(radians), lat2.apply(radians)

        # haversine公式
        dlon = lon2 - lon1
        dlat = lat2 - lat1
        a = (dlat / 2).apply(sin) ** 2 + lat1.apply(cos) * lat2.apply(cos) * (dlon / 2).apply(sin) ** 2
        c = 2 * a.apply(sqrt).apply(asin)
        r = 6371  # 地球平均半径，单位为公里
        # 返回单位为米
        return c * r * 1000

if __name__ == '__main__':
    gps = GPS()
    gps.get_gps_car()